﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Configuration;
using System.Data.SqlClient;
using System.Data;

//登录账号：xiketang001 密码：123456
//数据库脚本  
//数据脚本在：链接：https://pan.baidu.com/s/1r8oSbHxv_3m8UaOQCIQbDA 密码：fmg7
namespace MyCourseManageProject.Dal
{
   public  class SQLHelper
    {
       //定义数据库链接字符串(放到配置文件中)
       //private static string ConnStrng = "service=.;uid=sa;pwd=ok;database=CourseManageDB";
       //添加引用 Configuration;       
       private static string ConnString = ConfigurationManager.ConnectionStrings["SQLServerConnString"].ToString();

   
      /// <summary>
       /// 执行增删改操作的方法
      /// </summary>
       /// <param name="cmdText">SQL语句也可以是存储过程名称</param>
      /// <param name="param">参数数组(可有可无</param>
      /// <param name="isStoredProcedure">是否存储过程(默认false)</param>
      /// <returns>返回受影响的行数</returns>
       public static int Update(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)
       {
           //创建连接数据库对象
           SqlConnection conn = new SqlConnection(ConnString);
           //
           SqlCommand cmd = new SqlCommand(cmdText, conn);
           //参数不等于空
           if (param != null)
           {
               cmd.Parameters.AddRange(param);
           }
           //是否存储过程
           if (isStoredProcedure)
           {
               cmd.CommandType = CommandType.StoredProcedure;
           }
           try
           {

               conn.Open();
               //读取数据库
               return cmd.ExecuteNonQuery();
           }
           catch (Exception ex)
           {
               //添加日志,抛出异常
               throw new Exception("执行 public static int Update(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)方法时,出现异常:" + ex.Message);
           }
           finally
           {
               conn.Close();
           }
       }

       /// <summary>
       /// 执行返回一个只读结果集查询方法
       /// </summary>
       /// <param name="cmdText">SQL语句或存储过程</param>
       /// <param name="param">SQL语句或存储过程中的参数数组</param>
       /// <param name="isStoredProcedure">是否存在存储过程</param>
       /// <returns>返回未读取数据的Reader对象</returns>
       public static SqlDataReader GetReader(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)
       {
            //创建连接数据库对象
           SqlConnection conn = new SqlConnection(ConnString);
           //
           SqlCommand cmd = new SqlCommand(cmdText, conn);
           //参数不等于空
           if (param != null)
           {
               cmd.Parameters.AddRange(param);
           }
           //是否存储过程
           if (isStoredProcedure)
           {
               cmd.CommandType = CommandType.StoredProcedure;
           }
           try
           {

               conn.Open();
               //读取数据库
               return cmd.ExecuteReader(CommandBehavior.CloseConnection);
           }
           catch (Exception ex)
           {
               //添加日志,抛出异常
               throw new Exception("执行 public static SqlDataReader GetReader((string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)方法时,出现异常:" + ex.Message);
           }
          
       }

       /// <summary>
       /// 执行返回单一结果的方法;
       /// </summary>
       /// <param name="cmdText">SQL语句也可以是存储过程名称</param>
       /// <param name="param">参数数组(可有可无</param>
       /// <param name="isStoredProcedure">是否存储过程(默认false)</param>
       /// <returns>返回受影响的数值</returns>
       public static object GetScalar(string cmdText, SqlParameter[] param = null, bool isStoredProcedure = false)
       {
           //创建连接数据库对象
           SqlConnection conn = new SqlConnection(ConnString);
           //
           SqlCommand cmd = new SqlCommand(cmdText, conn);
           //参数不等于空
           if (param != null)
           {
               cmd.Parameters.AddRange(param);
           }
           //是否存储过程
           if (isStoredProcedure)
           {
               cmd.CommandType = CommandType.StoredProcedure;
           }
           try
           {
               conn.Open();
               //读取数据库
               return cmd.ExecuteScalar();
           }
           catch (Exception ex)
           {
               //添加日志,抛出异常
               throw new Exception("执行 public static object GetScalar(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)方法时,出现异常:"+ex.Message);
           }
           finally
           {
               conn.Close();
           }
       
    }


    }
}
